package com.wuzheng.wisgo.sanitation.cloud.repository.builder.facility;

import com.wuzheng.wisgo.sanitation.framework.util.StringUtils;
import org.apache.ibatis.jdbc.SQL;

/**
 * @author：hoipang
 * @date：Created in 2019-12-04
 * @description：构建环卫设施sql
 * @modified By：
 * @version: 1.0
 */
public class FacilityBuilder {

    /**
     * 根据条件构建查询环卫设施信息的sql语句
     *
     * @param name
     * @param code
     * @return
     */
    public String buildListFacilityDTOsSql(String id, String name, String code) {
        return new SQL() {
            {
                SELECT("fac.id, fac.facility_name AS facName, fac.facility_type_id AS facType");
                SELECT("fac.facility_type AS facTypeName, fac.dept_name AS deptName, pic.id AS imageId");
                SELECT("COALESCE(pic.picture_name,'') AS imageName, fac.address, COALESCE(fac.remark,'') AS remark");
                SELECT("fac.longitude, fac.latitude, COALESCE(pic.domain,'')||COALESCE(pic.picture_url,'') AS imageUrl");
                SELECT("EXTRACT(epoch FROM CAST(fac.create_time AS TIMESTAMP)) AS createTime");
                SELECT("EXTRACT(epoch FROM CAST(fac.modify_time AS TIMESTAMP)) AS modifyTime");
                SELECT("CASE fac.facility_type_id WHEN 'trash' THEN CASE WHEN CAST(FLOOR(random()*10) AS INT)>5 THEN 'finished' ELSE 'unfinished' END ELSE '' END AS status");
                FROM("ba_facility AS fac");
                LEFT_OUTER_JOIN("ba_picture AS pic ON fac.id = pic.peference_id AND pic.de_flag = FALSE");
                WHERE("fac.de_flag = FALSE");
                if (StringUtils.isNotBlank(id)) {
                    WHERE("fac.id = #{id}");
                }
                if (StringUtils.isNotBlank(name)) {
                    WHERE("fac.facility_name LIKE CONCAT('%', #{name}, '%')");
                }
                if (StringUtils.isNotBlank(code)) {
                    WHERE("fac.facility_type_id = #{code}");
                }
                ORDER_BY("fac.create_time DESC");
            }
        }.toString();
    }

    /**
     * 构建查询地图上所有垃圾桶分配状态的sql语句
     * @return
     */
    public String buildListTrashDistributionStateDTOsSql(){
        return new SQL(){
            {
                SELECT("fac.id, CASE WHEN COALESCE(trt.transfer_route_id,'')='' then 'unallocated' ELSE 'allocated' END AS status");
                FROM("ba_facility AS fac");
                LEFT_OUTER_JOIN("ba_transfer_route_trash AS trt on fac.id = trt.trash_id and trt.de_flag = FALSE");
                WHERE("fac.de_flag = FALSE");
            }
        }.toString();
    }

    /**
     * 构建查询地图上所有垃圾桶收运状态的sql语句
     * @return
     */
    public String buildListTrashClearStateDTOsSql(){
        return new SQL(){
            {
                SELECT("id, CASE WHEN CAST(FLOOR(random()*10) AS INT)>5 THEN 'finished' ELSE 'unfinished' END AS status");
                FROM("ba_facility AS fac");
                WHERE("fac.de_flag = FALSE AND fac.facility_type_id = 'trash'");
            }
        }.toString();
    }
}
